package com.chuangyiruanke.util;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;

import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * 使用CVS模式解决XLSX文件，可以有效解决用户模式内存溢出的问题
 */
public class CSVConverter {

  enum XSSFDataType {
    BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER,
  }

  class XSSFSheetHandler extends DefaultHandler {

    private int notNullIndex;
    private StylesTable stylesTable;
    private ReadOnlySharedStringsTable sharedStringsTable;
    private final BufferedWriter writer;
    private final int startColumn;
    private final int endColumn;
    private final int minColumnCount;
    private boolean vIsOpen;
    private XSSFDataType nextDataType;
    private short formatIndex;
    private String formatString;
    private final DataFormatter formatter;
    private int thisColumn = -1;
    private int lastColumnNumber = -1;
    private StringBuffer value;
    private String[] record;
    private boolean isCellNull = false;

    public XSSFSheetHandler(StylesTable styles, ReadOnlySharedStringsTable strings,
                            int startColumn, int endColumn, int notNullIndex, OutputStream output) {
      this.notNullIndex = notNullIndex;
      this.stylesTable = styles;
      this.sharedStringsTable = strings;
      this.startColumn = startColumn;
      this.endColumn = endColumn;
      this.minColumnCount = endColumn - startColumn + 1;
      OutputStreamWriter streamWriter = new OutputStreamWriter(output);
      this.writer = new BufferedWriter(streamWriter);
      this.value = new StringBuffer();
      this.nextDataType = XSSFDataType.NUMBER;
      this.formatter = new DataFormatter();
      record = new String[this.minColumnCount];
    }

    public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {

      if ("inlineStr".equals(name) || "v".equals(name)) {
        vIsOpen = true;
        // Clear contents cache
        value.setLength(0);
      }
      // c => cell
      else if ("c".equals(name)) {
        // Get the cell reference
        String r = attributes.getValue("r");
        int firstDigit = -1;
        for (int c = 0; c < r.length(); ++c) {
          if (Character.isDigit(r.charAt(c))) {
            firstDigit = c;
            break;
          }
        }
        thisColumn = nameToColumn(r.substring(0, firstDigit));
        if (thisColumn < startColumn || thisColumn > endColumn) {
          return;
        }

        // Set up defaults.
        this.nextDataType = XSSFDataType.NUMBER;
        this.formatIndex = -1;
        this.formatString = null;
        String cellType = attributes.getValue("t");
        String cellStyleStr = attributes.getValue("s");
        if ("b".equals(cellType)) {
          nextDataType = XSSFDataType.BOOL;
        } else if ("e".equals(cellType)) {
          nextDataType = XSSFDataType.ERROR;
        } else if ("inlineStr".equals(cellType)) {
          nextDataType = XSSFDataType.INLINESTR;
        } else if ("s".equals(cellType)) {
          nextDataType = XSSFDataType.SSTINDEX;
        } else if ("str".equals(cellType)) {
          nextDataType = XSSFDataType.FORMULA;
        } else if (cellStyleStr != null) {
          // It's a number, but almost certainly one
          // with a special style or format
          int styleIndex = Integer.parseInt(cellStyleStr);
          XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
          this.formatIndex = style.getDataFormat();
          this.formatString = style.getDataFormatString();
          if (this.formatString == null) {
            this.formatString = BuiltinFormats.getBuiltinFormat(this.formatIndex);
          }
        }
      }

    }

    public void endElement(String uri, String localName, String name) throws SAXException {
      String thisStr = null;
      // v => contents of a cell
      if ("v".equals(name)) {
        if (thisColumn < startColumn || thisColumn > endColumn) {
          return;
        }
        // Process the value contents as required.
        // Do now, as characters() may be called more than once
        switch (nextDataType) {
          case BOOL:
            char first = value.charAt(0);
            thisStr = first == '0' ? "FALSE" : "TRUE";
            break;
          case ERROR:
            thisStr = value.toString();
            break;
          case FORMULA:
            // A formula could result in a string value,
            // so always add double-quote characters.
            thisStr = value.toString() ;
            break;
          case INLINESTR:
            XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
            thisStr = rtsi.toString();
            break;
          case SSTINDEX:
            String sstIndex = value.toString();
            try {
              int idx = Integer.parseInt(sstIndex);
              XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx));
              thisStr = rtss.toString();
            } catch (NumberFormatException ex) {
              ex.printStackTrace();
            }
            break;
          case NUMBER:
            String n = value.toString();
            // 判断是否是日期格式
            if (HSSFDateUtil.isADateFormat(this.formatIndex, n)) {
              Double d = Double.parseDouble(n);
              Date date = HSSFDateUtil.getJavaDate(d);
              thisStr = formatDateToString(date);
            } else if (this.formatString != null)
              thisStr = formatter.formatRawCellContents(Double.parseDouble(n), this.formatIndex, this.formatString);
            else
              thisStr = n;
            break;
          default:
            thisStr = "(TODO: Unexpected type: " + nextDataType + ")";
            break;
        }
        // Output after we've seen the string contents
        // Emit commas for any fields that were missing on this row
        if (lastColumnNumber == -1) {
          lastColumnNumber = this.startColumn;
        }
        //判断单元格的值是否为空
        if (thisStr == null || "".equals(thisStr)) {
          isCellNull = true;// 设置单元格是否为空值
        }
        record[thisColumn - startColumn] = thisStr;       //添加
        // Update column
        if (thisColumn > -1) {
          lastColumnNumber = thisColumn;
        }
      } else if ("row".equals(name)) {
        // Print out any missing commas if needed
        if (minColumnCount > 0) {
          // Columns are 0 based
          if (lastColumnNumber == -1) {
            lastColumnNumber = 0;
          }
          // 判断是否空行
          if (!isCellNull && record[notNullIndex] != null) {
            isCellNull = false;
            write();
            for (int i = 0; i < record.length; i++) {
              record[i] = null;
            }
          }
        }
        lastColumnNumber = -1;
      }

    }

    public void characters(char[] ch, int start, int length) throws SAXException {
      if (vIsOpen) {
        value.append(ch, start, length);
      }
    }

    private int nameToColumn(String name) {
      int column = -1;
      for (int i = 0; i < name.length(); ++i) {
        int c = name.charAt(i);
        column = (column + 1) * 26 + c - 'A';
      }
      return column;
    }

    private String formatDateToString(Date date) {
      SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//格式化日期
      return sdf.format(date);
    }

    private void write() {
      try {
        for (int i = 0; i < minColumnCount; i++) {
          if (StringUtils.isNotEmpty(record[i])) {
            writer.write('"' + record[i].replaceAll("\"", "\"\"") + '"');
          }
          writer.write(",");
        }
        writer.write("\n");
      } catch (IOException ex) {
        ex.printStackTrace();
      }
    }

    public void close() throws IOException {
      this.writer.close();
    }
  }

  private OPCPackage pkg;
  private InputStream inputStream;
  private ReadOnlySharedStringsTable strings;

  public CSVConverter(String fileName) throws FileNotFoundException {
    this.inputStream = new FileInputStream(new File(fileName));
  }

  public CSVConverter(InputStream stream) {
    this.inputStream = stream;
  }

  private void processSheet(int startColumn, int endColumn, int notNullIndex, InputStream sheetInputStream,
                            StylesTable styles, OutputStream output) throws IOException, ParserConfigurationException, SAXException {
    InputSource sheetSource = new InputSource(sheetInputStream);
    SAXParserFactory saxFactory = SAXParserFactory.newInstance();
    SAXParser saxParser = saxFactory.newSAXParser();
    XMLReader sheetParser = saxParser.getXMLReader();
    XSSFSheetHandler handler = new XSSFSheetHandler(styles, strings, startColumn, endColumn, notNullIndex, output);   //内部类
    sheetParser.setContentHandler(handler);
    sheetParser.parse(sheetSource);
    handler.close();
  }

  /**
   * 初始化这个处理程序 将
   *
   * @throws IOException
   * @throws OpenXML4JException
   * @throws ParserConfigurationException
   * @throws SAXException
   */
  private void process(String sheetName, int startColumn, int endColumn, int notNullIndex, OutputStream output)
      throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {
    XSSFReader xssfReader = new XSSFReader(this.pkg);
    StylesTable styles = xssfReader.getStylesTable();
    XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();

    while (iter.hasNext()) {
      InputStream stream = iter.next();
      String sheetNameTemp = iter.getSheetName();
      if (sheetName.equals(sheetNameTemp)) {
        processSheet(startColumn, endColumn, notNullIndex, stream, styles, output);
        stream.close();
        break;
      }
    }
  }

  public void close() throws IOException {
    pkg.close();
  }

  /**
   * 读取Excel
   *
   * @param startColumn  第一列
   * @param endColumn    最后一列
   * @param notNullIndex 判断那一列数据不为空（此列不为空，则整行数据有效）
   * @return
   * @throws SAXException
   * @throws ParserConfigurationException
   * @throws OpenXML4JException
   * @throws IOException
   */
  public void convert(String sheetName, int startColumn, int endColumn, int notNullIndex,
                      OutputStream output) throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {
    if (pkg == null) {
      pkg = OPCPackage.open(inputStream);
      strings = new ReadOnlySharedStringsTable(this.pkg);
    }
    this.process(sheetName, startColumn, endColumn, notNullIndex, output);
  }
}
